package cn.gson.zuche.model.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import cn.gson.zuche.model.bean.Huizongbiao;
import cn.gson.zuche.model.jdbc.MySqlDb;
import cn.gson.zuche.model.jdbc.ParamHandler;
import cn.gson.zuche.model.jdbc.ResultHandler;

public class Huizongdao implements ResultHandler<Huizongbiao>{
  private MySqlDb db=MySqlDb.getInstance();
  
  public List<Huizongbiao> select() throws SQLException{
	  String sql="select * from huizonbiao";
	  return db.executeQuery(sql, this);	  
  }
  
  /**
   * 查询每个月的总钱
   * @param b
   * @return
   * @throws SQLException
   */
  public List<Huizongbiao> selectsummoney(String y) throws SQLException{
	  String sql="SELECT SUM(money) as money from huizonbiao WHERE `data` BETWEEN '2017-"+y+"-1' and '2017-"+y+"-31'";
	  return db.executeQuery(sql, this);	  
  }
  
  
  public boolean insert(final Huizongbiao b) throws SQLException{
	  String sql="insert into huizonbiao(data,money) values(?,?)";
	  System.out.println(b);
	return db.executeUpdate(sql, new save(b))>0;
	  
  }
  private class save implements ParamHandler{
	  Huizongbiao b;
	 public save(Huizongbiao b){
		  this.b=b;
	}
	   	 
	@Override
	public void doHander(PreparedStatement pStatement) throws SQLException {
	pStatement.setString(1, b.getName());
	pStatement.setString(2, b.getMoney());	
	}
	  
  }
  
  public List<Huizongbiao> select(String data) throws SQLException{
	  String sql="select * from huizonbiao where data='"+data+"'";
	  return db.executeQuery(sql, this);	  
  }	
  
	@Override
	public Huizongbiao doHander(Map<String, Object> row) {
		Huizongbiao h=new Huizongbiao();
		h.setName(row.get("data")+"");
		h.setMoney(row.get("money")+"");
		return h;
	}
}
